CODEWITHSUNDEEP
mysqlsqljoin
janues
janues

Reputation: 3

SQL and Joining

Read below and navigate to this url http://sqlfiddle.com/#!2/e825f to get a better understanding of my issue.

I have been trying to get this query right for awhile but cannot figure it out. I have been trying to join two tables to get the right data, but maybe I think I should make a third table. See my situation below:

I have two tables:

  1. sites table - lists all websites that I have. See below for how I created my table

    create table sites
(
  id int,
  websiteName varchar(50),
  url varchar(50),
  mobile_id varchar(50),
  is_responsive varchar(1)    
);

  2. Mobiles table - lists which websites are mobile and has a mobile url

    create table mobile
(
 id int,
 mobile_url varchar(50)
);

The sites table and mobiles table are related through the foreign key in the sites table called [mobile_id].

The is_responsive column located in the [sites table] is a bit field that holds a 1, stating this site is fully responsive, or a 0 stating this site is non-responsive.

MY GOAL: To build a query that returns all mobile websites and responsive websites. I tried using three tables but that did not work. Originally my third table held all responsive websites, but adding another table does not make sense. Also I found a solution using the UNION statement, but I do not want to use that.

Upvotes: 0

Views: 65

Answers (5)

spencer7593
spencer7593

Reputation: 108530

Based on your description of the specification... "all mobile websites and responsive websites"...

I will suggest that something of this form may return the resultset you specified.

SELECT s.id
     , s.websiteName
     , s.url
     , s.mobile_id
     , s.is_responsive
     , IF(m.mobile_url IS NOT NULL,'1','0') AS is_mobile
  FROM sites s
  LEFT
  JOIN mobile m
    ON m.mobile_url = s.mobile_id 
 WHERE m.mobile_url IS NOT NULL
    OR s.is_responsive = '1'

We'd normally expect the foreign key to reference the id column, but given that you say you have a foreign key (and don't specify which column is the target), and the foreign key column has a datatype of VARCHAR(50), we are going to guess that this references the mobile_url column, since that is the only column in mobile that has a matching datatype.

If the intent is to reference the id column, then the datatype of the mobile_id should match the datatype of the id column, and the join predicate would be:

    ON m.id = s.mobile_id

Also, each of your tables should have a PRIMARY KEY, or at least a UNIQUE KEY defined.table.

The table definitions I would use, based on what you posted, would be something along these lines:

 CREATE TABLE mobile
 ( id           INT UNSIGNED NOT NULL PRIMARY KEY COMMENT 'PK'
 , mobile_url   VARCHAR(50)                       COMMENT 'mobile website URL'
 ) ;

 CREATE TABLE site
 ( id           INT UNSIGNED NOT NULL PRIMARY KEY COMMENT 'PK'
 , websiteName  VARCHAR(50)                       COMMENT 'website name'
 , url          VARCHAR(50)                       COMMENT 'website URL'
 , mobile_id    INT UNSIGNED                      COMMENT 'FK ref mobile.id'
 , is_responsive TINYINT(1) NOT NULL DEFAULT 0    COMMENT 'boolean'
 , CONSTRAINT FK_site_mobile (mobile_id) REFERENCES mobile(id)
 ) ;

Upvotes: 1

Smutje
Smutje

Reputation: 18183

select *
from sites
left outer join mobile on sites.mobile_id = mobile.id
where sites.is_responsive = '1' or mobile.mobile_url is not null;

Upvotes: 0

Abkarino
Abkarino

Reputation: 1446

The program in the sample is that no mobile site is responsive but the code you wrote first is working.

 select sites.* from sites
 inner join mobile on sites.mobile_id = mobile.id 
 where sites.is_responsive = '1'

Upvotes: 0

agad
agad

Reputation: 2189

select mobile_url from from mobile 
union
select url from sites when is_responsive = 1

Upvotes: 0

NMK
NMK

Reputation: 1020

Try something like this

select sites.* from sites
left join mobile on mobile.id = sites.mobile_id
where (sites.is_responsive = '1' or mobile_id is not null)

Upvotes: 1

Related Questions