MySQL query - Using multiple tables

Question

I have 4 tables. (Simplified the 4 tables for the question)

Table 1: Supplier - Id, Type, TypeId, ExpiryDate

Table 2: SupplierType1 - Id, UserId, SupplierName

Table 3: SupplierType2 - Id, UserId, SupplierName

Table 4: User - Id, Name

All Id's are simply auto-increments in their tables.

In Supplier: 'Type' refers to either SupplierType1 or SupplierType2

'TypeId' is the 'Id' from either SupplierType1 or SupplierType2.

In both SupplierType1 and SupplierType2: 'UserId' is the Id from User

I have the 'UserId' (shown as X below). I want to return a list of which has these columns: Supplier.Id, SupplierType1.RetailName, ExpiryDate

Where (SupplierType1.UserId == 'X' AND Supplier.TypeId == SupplierType1 AND Supplier.ExpiryDate < CurrentDate)

I've only recently starting using mysql and my current solutions seem way too cumbersome. Keen to learn a better way to do this. I would appreciate the help. Thanks in advance!

J.K · Accepted Answer · 2016-03-28 09:09:41Z

2

query

$qry = "
SELECT * 
FROM Supplier s
LEFT JOIN SupplierType1 s1 ON s1.id=s.TypeId
LEFT JOIN User u ON u.Id=s1.UserId
WHERE s1.UserId='X' AND s.TypeId='SupplierType1' AND s.ExpiryDate<CURDATE()
";

answered Mar 28, 2016 at 9:09

J.K

1,3741 gold badge12 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Diego Over a year ago

Thanks Devs, perfect!

Collectives™ on Stack Overflow

MySQL query - Using multiple tables

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related