joining tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

joining tables

Author

Topic

SGB19
Starting Member

10 Posts

Posted - 2007-03-02 : 13:23:51

I am new in SQL and want to find the projection for these queries. I have four tables, CUSTOMER, INVOICE, INVOICEITEMLINEPURCHASED, STOVE and want to find all the stoves that were never purchased by customers in a specific state like PA. I was trying to write the queries but I ended with something that gave me all the stoves bought by the customers. I need to find the reverse as all the stoves that were never been bought by these customers.Here is the query:
SELECT type+version as 'Stove type/version never purchased by PA customers'
from COMPUTER
WHERE SERIALNUMBER IN
(SELECT fk_STOVENBR
FROM INV_LINE_ITEM
WHERE FK_INVOICENBR IN
(SELECT INVOICENBR
FROM INVOICE
WHERE FK_CUSTOMERID IN
(SELECT CUSTOMERID
FROM CUSTOMER
WHERE STATEPROVINCE = 'pa')));

Any comment is appreciated and welcome.

sgb19

********
***sg***
********

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-02 : 14:26:01

Try this

SELECT type+version as 'Stove type/version never purchased by PA customers'
from STOVE S
LEFT OUTER JOIN INV_LINE_ITEM LI ON S.SERIALNUMBER = LI.fk_STOVENBR
LEFT OUTER JOIN INVOICE I ON LI.FK_INVOICENBR = I.INVOICENBR
LEFT OUTER JOIN CUSTOMER C ON I.FK_CUSTOMERID = C.CUSTOMERID AND C.STATEPROVINCE = 'pa'
WHERE LI.fk_STOVENBR IS NULL

SGB19
Starting Member

10 Posts

Posted - 2007-03-02 : 16:37:01

I tried it but did not get the expected result. I will keep on working on it. Any other comment is welcome.

sgb19

********
***sg***
********

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-02 : 17:45:17

If you post your table structures with some sample data and the expected results we can help you.

SGB19
Starting Member

10 Posts

Posted - 2007-03-05 : 11:26:35

[quote]Originally posted by SGB19
Any comment is appreciated and welcome.

Here is the table structure for CUSTOMER:
CustomerID Name StreetAddress
State
ZipCode
----------- ----------------------------------------------

Here is the table structure for INVOICE:
InvoiceNbr InvoiceDt
TotalPrice
CustomerID
EmpID

Here is the table structure for INV_LINE_ITEM
LineNbr
Quantity
InvoiceNbr
PartNbr
StoveNbr
ExtendedPrice

Here the table structure for stove:
SerialNumber Type
Version DateOfManufacture
Color
EmpId

I need to find all the stoves that were never been bought by these customers.Here is the query:

SELECT type+version as 'Stove type/version never purchased by PA customers'
from STOVE
WHERE SERIALNUMBER IN
(SELECT STOVENBR
FROM INV_LINE_ITEM
WHERE INVOICENBR IN
(SELECT INVOICENBR
FROM INVOICE
WHERE CUSTOMERID IN
(SELECT CUSTOMERID
FROM CUSTOMER
WHERE STATEPROVINCE = 'pa')));

I tried these queries but I did not get the results expected. Any comment is welcome.

Thanks

sgb19

********
***sg***
********

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-05 : 13:20:28

So you got half way there, you posted table structures, but still no sample data. You say you aren't getting the results you expect, well perhaps it's your expectations that need to change - but I really don't know because I can't get guess what results you're expecting or what results you're getting.

SGB19
Starting Member

10 Posts

Posted - 2007-03-05 : 17:00:32

quote:
Originally posted by snSQL

So you got half way there, you posted table structures, but still no sample data. You say you aren't getting the results you expect, well perhaps it's your expectations that need to change - but I really don't know because I can't get guess what results you're expecting or what results you're getting.

I need to find the highest number sold in the non popular color of stove and should have this projection:

Employee Quantity Color
---------------------- -------- ------------
Fred Bailey 3 GRAY
(1 row(s) affected)

Here are my queries:
SELECT top 1 s.color, e.name, count(*)
FROM INV_LINE_ITEM inv, stove s, employee e, invoice i
where s.serialnumber = inv.fk_stovenbr and inv.fk_invoicenbr = invoicenbr and i.fk_empid = empid
and color <(
select top 1 color
from inv_line_item inv, stove s, invoice i)
group by e.name, s.color
order by count(*) asc;

my projection is wrong compare to the expected projection:
color name
------------ -------------------------------------------------- -----------
BLUE Fred Bailey 1

(1 row(s) affected)

If someone can review my query, I will appreciate it. I am new to this thing.

2nd question I want to find the employee who has sold the least number of stoves. I did try to start to write the queries because I do not have a clue how to express "the least" in SQL

Here is the table structure for CUSTOMER:
CustomerID Name StreetAddress
State
ZipCode
----------- ----------------------------------------------

Here is the table structure for INVOICE:
InvoiceNbr InvoiceDt
TotalPrice
CustomerID
EmpID

Here is the table structure for INV_LINE_ITEM
LineNbr
Quantity
InvoiceNbr
PartNbr
StoveNbr
ExtendedPrice

Here is the table structure for stove:
SerialNumber Type
Version DateOfManufacture
Color
EmpId

********
***sg***
********

SGB19
Starting Member

10 Posts

Posted - 2007-03-05 : 17:04:02

quote:
Originally posted by snSQL

So you got half way there, you posted table structures, but still no sample data. You say you aren't getting the results you expect, well perhaps it's your expectations that need to change - but I really don't know because I can't get guess what results you're expecting or what results you're getting.

Hi snSQL,

Thank you for your input. I have a lot going on this project and do not know where to start. I posted some output of what I needed and here is some more.

Any comment is appreciated and welcome.

Here is the table structure for CUSTOMER:
CustomerID Name StreetAddress
State
ZipCode
----------- ----------------------------------------------

Here is the table structure for INVOICE:
InvoiceNbr InvoiceDt
TotalPrice
CustomerID
EmpID

Here is the table structure for INV_LINE_ITEM
LineNbr
Quantity
InvoiceNbr
PartNbr
StoveNbr
ExtendedPrice

Here is the table structure for stove:
SerialNumber Type
Version DateOfManufacture
Color
EmpId

I need to find all the stoves that were never been bought by these customers. I wrote these queries but the projection was wrong.

SELECT type+version as 'Stove type/version never purchased by PA customers'
from STOVE
WHERE SERIALNUMBER IN
(SELECT STOVENBR
FROM INV_LINE_ITEM
WHERE INVOICENBR IN
(SELECT INVOICENBR
FROM INVOICE
WHERE CUSTOMERID IN
(SELECT CUSTOMERID
FROM CUSTOMER
WHERE STATEPROVINCE = 'pa')));

I tried these queries but I did not get the results expected. Any comment is welcome.

Thanks

sgb19

********
***sg***
********

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-05 : 17:44:26

I'll try this one more time, have a look at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74221

Don't worry about what is being asked, just how the person asked the question. If you can ask your question like that, then you'll get an answer here.

SGB19
Starting Member

10 Posts

Posted - 2007-03-05 : 20:33:12

quote:
Originally posted by snSQL

I'll try this one more time, have a look at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74221

Don't worry about what is being asked, just how the person asked the question. If you can ask your question like that, then you'll get an answer here.

Let me try again. I have a client who needs a report on all stoves type and version that were never bought by his Pennsylvania customers. Then he needs the name of his employee who has sold more stoves in the non popular color. There are five tables involved:

CUSTOMER INVOICE.TABLE INV_LINE_ITEM.TABLE STOVE.TABLE
CustomerID invoicenbr invoicenbr serialnumber
Name invoicedt stovenbr type
State CustomerID Line version
ZipCode EmpID ExtendedPrice color
EmpID

EMPLOYEE.TABLE
EmpID
Name

I tried to find the stove type and version never purchased with this query
SELECT CAST(type as char(15)) + ' ' + cast(version as char(7))'Stove Type/Version never purchased by CA customers'
from stove, inv_line_item, invoice, customer
where serialnumber = fk_Stovenbr and fk_invoicenbr = invoicenbr and fk_customerid = customerid and stateprovince = 'ca';

I got this projection:
Stove Type/Version never purchased by CA customers
--------------------------------------------------
FiredAlways 2
FiredAlways 2
FiredAlways 2
FiredAlways 1
FiredNow 1
FiredAlways 1
FiredNow 2
FiredAtCamp 3

(8 row(s) affected)

But the Expected result for the stove that are never purchased by the customers in PA is:

Stove type/version never purchased by PA customers
--------------------------------------------------
FiredAlways 3
FiredAlways 4
FiredAlways 5
FiredAtCamp 1
FiredAtCamp 2
FiredNow 3

I got something different compare to the expected result.

I did not try to start this query for the employee who have sold more stoves in the least color but here is

Expected Result:

Employee Quantity Color
---------------------- -------- ------------
Fred Bailey 3 GRAY

Thank you in advance for the help.

********
***sg***
********

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-06 : 00:33:11

I'm actually going to try again. Let me try to explain it this way, if I was to ask you why my answer is wrong, I am trying to calculate an amount and I keep getting $15 but that's wrong, I should be getting $12, can you tell me what I'm doing wrong?

What you are asking is the SQL equivalent. Can you see how you would not be able to help me, because I'm telling you the right answer and I'm telling you the wrong answer but I'm not telling you how I'm getting to either one. That's what you're doing, unless you give me the data you are working on, I cannot help you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources