How to Join 4 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

General SQL Server Forums

New to SQL Server Programming

How to Join 4 tables

Author

Topic

NijelJ22
Starting Member

18 Posts

Posted - 2008-08-23 : 02:18:46

i have 2 queries ....

1) I have to write a query. I have a field name called "Flag" which is used only to track the status and hence stores "0" and "1". I want to capture the system date when user enter "1" in this field.Is it possible if yes then how?

2) I want to write a query that involves 4 tables. viz...
Table A, Table B, Table C ,Table D.
Table D have only 1 field and also that filed does not exists in any of the other table.

How can i use Table 4 field in my query as I need the value of that field as well in my output?

Thanks in Advance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-23 : 02:19:34

How do you expect to link the tables if TableD has no relation to the others? Could you show us some sample data?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

NijelJ22
Starting Member

18 Posts

Posted - 2008-08-23 : 02:25:47

Vendor --> vendorid , vendorcode , vendorname , username , contactperson , phone, fax

Payment --> paymentid , chknumber , bankname , paymentdate , paymentamount , vendorid

Invoice --> invoiceid, vendorid , invoicedate , currencyid , invoicevalue , locationid

Barcode --> barcodeid

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-23 : 02:39:00

Error at line 7.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

NijelJ22
Starting Member

18 Posts

Posted - 2008-08-23 : 02:41:18

Hi Tkizer,

I didn't get that? What was that ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-23 : 04:44:09

quote:
Originally posted by NijelJ22

Vendor --> vendorid , vendorcode , vendorname , username , contactperson , phone, fax

Payment --> paymentid , chknumber , bankname , paymentdate , paymentamount , vendorid

Invoice --> invoiceid, vendorid , invoicedate , currencyid , invoicevalue , locationid

Barcode --> barcodeid

How about some sample data and the result that you want ?

Please also explain the purpose of the required query

KH
[spoiler]Time is always against us[/spoiler]

NijelJ22
Starting Member

18 Posts

Posted - 2008-08-23 : 05:17:16

Output:

VendorName VendorCode InvoiceDate PaymentID PaymentDate BarcodeNumber
(Vendor Table) (Vendor Table) (Invoice Table) (Payment Table) (Payment Table) (Barcode Table)

Testven 12 March 21, 2008 9 April 15, 2008 1000000000
K A Electronics 33 April 29, 2008 15 May 15, 2008 1000000001
G.T Enterprises 22 April 23, 2008 24 May 15, 2008 1000000070
Kinfotech Ltd 98 May 2, 2008 25 June 15, 2008 1000000678
IBM Ltd. 23 May 8, 2008 36 June 15, 2008 1000000345

Every transaction should have one bar code number associated with it.

Regards

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-23 : 05:24:01

[code]SELECT v.VendorName,
v.VendorCode,
i.InvoiceDate,
p.PaymentID,
p.PaymentDate,
b.BarcodeNumber
FROM vendor v
INNER JOIN payment p
ON p.vendorid=v.vendorid
INNER JOIN invoice i
ON i.vendorid =v.vendorid
CROSS JOIN Barcode b[/code]

Not sure how you link this to barcode as none of the tables have barcodeid field. Can you explain how you will decide what barcode id will be linked to each record of above resultset?I've just put CROSS JOIN which case it will repeat the entire resultset for each value of barcodenumber

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-23 : 06:02:07

quote:
Originally posted by NijelJ22

Output:

VendorName VendorCode InvoiceDate PaymentID PaymentDate BarcodeNumber
(Vendor Table) (Vendor Table) (Invoice Table) (Payment Table) (Payment Table) (Barcode Table)

Testven 12 March 21, 2008 9 April 15, 2008 1000000000
K A Electronics 33 April 29, 2008 15 May 15, 2008 1000000001
G.T Enterprises 22 April 23, 2008 24 May 15, 2008 1000000070
Kinfotech Ltd 98 May 2, 2008 25 June 15, 2008 1000000678
IBM Ltd. 23 May 8, 2008 36 June 15, 2008 1000000345

Every transaction should have one bar code number associated with it.

Regards

Please POST SAMPLE DATA for each of the TABLE. You only posted what you want. How is it possible to know how to get that without the knowledge of your data in each of the table ? ?

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources