Help with Select query

This should be simple I think but I am no expert so maybe one of you will have the kindness to help me a bit. I have two tables(System, NAIC) and both have the primary key SystemId.

I need to gell all the rows from the table system and anything that correspond from the table NAIC, if no correspondant systemId the return "" or nothing in the fields of NAIC

Thank you,

Table:
System
-SystemId*
-Company
-Reseller
-SystemType
...

NAIC
-SystemId*
-NAIC_1
-NAIC_2
-NAIC_3
-NAIC_4

I think what you need is a basic LEFT JOIN.

SELECT

SystemId
Company
Reseller
SystemType

...

FROM

[system]

LEFT OUTER JOIN NAIC

ON [System].Systemid = NAIC.SystemId

sql

Help with Report Filter on bit field

I have this filter for my report table:

expression operator Value

=Cstr(Fields!work.Value) = 'True'

My report's table isn't returning data, but in preview but if I run the dataset, there is clearly some valid records tha contain 'true' for the field Work. The field work in my SQL Server table is type bit

additional screenshot here: http:\\www.webfound.net\no_data_filter_on_bit_field.jpg

I would try the following filter:

Expression:
=CBool(Fields!work.Value)

Operator:
=

Value:
=True

Note that the filter value is an expression (=True).

-- Robert

|||

Robert, thanks very much, it works. Now let me ask you this. I had a total textbox that just did a COUNT(number). But I need to do a COUNT on number if CBool(Fields!work.Value) = True. I was wondering how to form an if statement behind my text field to do this. number is just the identity field in which I can count on.

|||

I tried this but it's malformed:

=IIf(CBool(First(Fields!home.Value, "Mismatch_Data")) == True, COUNT(Fields!number.Value, "Mismatch_Data"), 0)

|||

Do you really want to make the decision for the count based on the first data row value of the "home" field? If yes, then this expression should work (note - since RDL expressions are VB.NET based the comparison only needs one '='; in this particular case you can also omit it):

=IIf(CBool(First(Fields!home.Value, "Mismatch_Data")), COUNT(Fields!number.Value, "Mismatch_Data"), 0)

Also, are you really looking for the Count or for a Sum aggregate?

If you want to sum individual rows based the value of the "home" field in that particular row (rather than just looking at the first row), you would use conditional aggregation and the following expression would need to be put e.g. into a table header/footer bound to the Mismatch_Data dataset):

=Sum( iif(CBool(Fields!home.Value), Fields!number.Value, 0)

-- Robert

|||

Thanks, Robert. I was looking for the count of how many records were found. I have 2 tables....so I needed a count of records using the number field which was a unique field.

That should work...

help with query

hi all
Need a bit of help/direction with an sql query
i have a table with customer details and in another with customer orders. I need to show all the orders in the same field based upon the id from the customer details table.In other words to piggy back all the orders in one field.Do i use some sort of sub query?
if i have :

customer orders:

custID Order
c1 Vauxhall
c1 ford
c1 VW
c1 BMW

so that i get:

customer car orders
c1 Vauxhall ford VW BMW


How can i achieve this? thanks daveThe best way to do a pivot is using the client software. It can be done reasonably cleanly on the server using database engine specific code. It can also be done with pure SQL, but that requires some assumptions and some rather ugly code.

The short answer is: you should really do this on the client.

-PatP|||Why not write a function that returns a list of orders and use it in the select list.
create function get_orders(i_custid VARCHAR2) RETURN VARCHAR2
IS
retval VARCHAR2(999);
BEGIN
for rec in (select order from customer_orders where custid = d_custid)
loop
retval := retval || ' '|| order;
end loop;
return retval;
end;

select distinct custid customer, get_orders(custid) "car orders"
from customer_orders;

Now, that's oracle specific but you get the gist.|||in sybase asa, use the list (http://sybooks.sybase.com/onlinebooks/group-sas/awg0802e/dbrfen8/@.Generic__BookTextView/22251;pt=14246/*;nh=1?DwebQuery=LIST+function&DwebSearchAll=1) function:
select customers.custID
, list(custorder)
from customers
left outer
join orders
on customers.custID
= orders.custID
group
by customers.custid

in mysql 4.1, use the group_concat (http://dev.mysql.com/doc/mysql/en/GROUP-BY-Functions.html) function:
select customers.custID
, group_concat(custorder)
from customers
left outer
join orders
on customers.custID
= orders.custID
group
by customers.custid

in other less advanced databases, write a program

;)|||Arrr, maybe we ought to start with why would you ever need this?
If I was to design my own DBMS I would have put this type of function on the LRU list and flush out of SGA ASAP.
:D|||thanks.. all your help is much appriciated ;)|||thanks.. all your help is much appriciated ;)
What DB are you on anyways?