Complex table Join at sql

Question

Pls i want to get data from Articles Table according to PressKit Table Data

i need help how to join between Articles Table and PressKit Table

while i have to different intermediate Tabled between them?

i want to get data like that

 SELECT ArticleID ,
        ArticaleTile
 FROM   Articles
 WHERE  dbo.MagazineID IN (
        SELECT  dbo.Magazines.MagazineID
        FROM    dbo.Magazines
                INNER JOIN dbo.PressKitMagazines ON dbo.Magazines.MagazineID = dbo.PressKitMagazines.MagazineID
        WHERE   PressKitID = @PressKitID )
        AND ClassID IN (
        SELECT  Class.classID
        FROM    dbo.Class
                INNER JOIN dbo.PressKitClass ON Class.classID = PressKitClass.classID
        WHERE   PressKitID = @PressKitID )

Select ArticleID,ArticaleTile from Articles ......... ..... where PressKitID=@PressKitID i need help at join statment — M.Junior, Jan 16 '16 at 02:25
"get data from ... Table according to .. Table Data" & "join between ... Table and ... Table" are not clear. Use more words and simple sentences that explain what you mean instead of vague words like "get" & "according to" & "between". What does a row say when in each table? What does a row say when in the result of your query? Show your table definitions and example input & output. — philipxy, Jan 16 '16 at 02:41

score 0 · Answer 1 · answered Jan 16 '16 at 05:11

0

Give this a try. It just does an INNER JOIN to the tables with the PressKitID you're looking for and then a simple WHERE clause can determine the specific parameter you want:

SELECT 
    a.ArticleID, a.ArticleTitle
FROM articles AS a
INNER JOIN PressKitMagazines AS m
ON a.magazineid = m.magazineid
INNER JOIN PressKitClass AS c
ON a.classid = c.classid
WHERE
    m.PressKitID = @PressKitID
    OR
    c.PressKitID = @PressKitID

answered Jan 16 '16 at 05:11

Jericho

213
1
10

The original query returns articles with a magazine *and* class from those of the given press kit. This returns articles with either a magazine or a class from those of the given press kit. – philipxy Jan 16 '16 at 23:45
Agreed. I took the original question (not query) as wanting the article table information if there exists a matching @PressKitID so I used OR so it will return a result regardless of whether there is both a magazine and class, or just one. If it does require both a magazine and class then change the OR to AND in the WHERE clause. I'll update my query if the original post is edited with additional specifics. – Jericho Jan 17 '16 at 16:23

score 0 · Answer 2 · answered Jan 16 '16 at 10:40

It is not clear what the problem is however I suggest using EXISTS, like this:

SELECT
      A.ArticleID
    , A.ArticaleTile
FROM Articles AS A
WHERE EXISTS (
            SELECT NULL
            FROM dbo.Magazines AS M
                  INNER JOIN dbo.PressKitMagazines AS PKM ON M.MagazineID = PKM.MagazineID
            WHERE M.PressKitID = @PressKitID
                  AND A.ArticleID = M.MagazineID
      )
    OR EXISTS (
            SELECT NULL
            FROM dbo.Class AS C
                  INNER JOIN dbo.PressKitClass PKK ON C.classID = PKK.classID
            WHERE C.PressKitID = @PressKitID
                  AND A.ClassID = C.ClassID
      )

I also believe you need to use OR between those two subqueries in the where clause.

score 0 · Accepted Answer · edited May 23 '17 at 12:07

SELECT ArticleID, ArticleTitle
FROM Articles a
JOIN dbo.PressKitMagazines pm
ON a.MagazineID = pm.MagazineID AND pm.PressKitID = @PressKitID
JOIN dbo.PressKitClass pc
ON a.ClassID = pc.ClassID AND pc.PressKitID = @PressKitID

You do not need to involve Magazines, Class or PressKit because you do not need to return the Names associated with their IDs. You didn't need them in your original query either:

SELECT ArticleID, ArticleTitle
FROM Articles
WHERE MagazineID IN (
    SELECT MagazineID
    FROM dbo.PressKitMagazines
    WHERE PressKitID = @PressKitID )
AND ClassID IN (
    SELECT classID
    FROM dbo.PressKitClass
    WHERE PressKitID = @PressKitID )

Suppose that table T holds the rows that make some statement parameterized by its columns into a true statement. Eg Articles holds the rows that satisfy:
    "article ArticleID titled ArticleTitle was in magazine MagazineID & was of class ClassID"
or in shorthand:
    Article(ArticleID, ArticleTitle, MagazineID, ClassID)
Inside an SQL SELECT statement, a table's column names are prefixed by an alias and a dot (which can be left out when there's no ambiguity):
    a(a.ArticleID, a.ArticleTitle, a.MagazineID, a.ClassID)
So the rows in:
    SELECT C FROM U WHERE C IN (SELECT C FROM T WHEREcondition)
or with prefixes and dots:
    SELECT U.C FROM U WHERE U.C IN (SELECT T.C FROM T WHEREcondition)
are the rows where:
    SELECT U.C FROM U JOIN T ONconditionAND U.C = T.C
because they both return rows with just column C where:
    U(...,U.C,...) AND T(...,T.C,...) AND condition AND U.C = T.C

For composing queries see this answer.

Complex table Join at sql

3 Answers3